package org.lib.jpamysql.query;

import java.util.List;

import javax.persistence.NamedQuery;

import org.lib.jpamysql.query.dto.NamesDTO;
import org.lib.jpamysql.query.vo.GroupbyAgeVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * 注解式查询方法
 * [@Query} {@Param} 等
 * JPQL vs 原生SQL
 * @author ben
 * @date 2021-10-30 17:28:30 CST
 */
@Repository(value = "user3Dao")
public interface User3DAO extends JpaRepository<User, Long> {

	//
	// JPQL
	
	@Query(value="SELECT u FROM User u WHERE u.firstName = ?1")
	// 不支持 LIMIT！
//	// 启动失败：QuerySyntaxException
//	@Query(value="SELECT u FROM User u WHERE u.firstName = ?1 LIMIT 1")
//	// 启动失败：QuerySyntaxException
//	@Query(value="SELECT u FROM User u WHERE u.firstName = ?1 LIMIT 0,1")
	User find1User(String firstName);
	
	// 排序
	// 注，不需要使用 ?2 
	@Query(value="SELECT u FROM User u WHERE u.firstName = ?1")
	List<User> findUserList(String firstName, Sort sort);
	
	// 根据 firstName 模糊查询（默认忽略大小写）
	@Query(value="SELECT u FROM User u WHERE u.firstName like %?1%")
	// 区分大小写 启动失败 TODO
//	@Query(value="SELECT u FROM User u WHERE u.firstName COLLATE latin1_bin like %?1%")
	// 区分大小写 启动失败
//	@Query(value="SELECT u FROM User u WHERE u.firstName like %?1% COLLATE latin1_bin")
	List<User> findLikeByFirstName(String keywords);
	
	// 分页
	@Query(value="SELECT u FROM User u WHERE u.age > ?1")
	Page<User> pageUserGreaterThan(Integer age, Pageable pageable);
	
	// @Param
	@Query(value="SELECT u FROM User u WHERE u.firstName = :firstName and u.lastName = :lastName")
	List<User> findByNames(@Param(value = "lastName") String name2, @Param(value = "firstName") String name1);
	
	// 使用 对象接收参数 + SpEL
	@Query(value="SELECT u FROM User u WHERE u.firstName = :#{#dto.name1} and u.lastName = :#{#dto.name2}")
	List<User> findByNamesDto(@Param(value = "dto") NamesDTO dto);
	
	// 聚合查询
	// 必须 AS、必须使用 COUNT(1) >、返回值必须是Object[]（使用GroupbyAgeVO失败，没有转换器-怎么弄？TODO）；
	@Query(value="SELECT u.age,COUNT(1) AS count FROM User u GROUP BY u.age HAVING COUNT(1) > ?1", countProjection = "")
	List<Object[]> groupbyAge(Long nums);
	
	// SpEL表达式支持
	@Query(value="SELECT u FROM #{#entityName} u WHERE age = ?1")
	List<User> findBySpel(Integer age);
	
	//
	// 原生SQL
	
	@Query(value="SELECT * FROM user WHERE first_name = ?1 LIMIT 1", nativeQuery = true)
	User find1UserSql(String firstName);

	// 排序
	// 错误方式：启动失败
	// 
//	@Query(value="SELECT * FROM user WHERE first_name = ?1 ORDER BY ?2", nativeQuery = true)
//	List<User> findUserListSql(String firstName, Sort sort);
	// 正确方式：不能使用 DESC
	@Query(value="SELECT * FROM user WHERE first_name = ?1 ORDER BY ?2 ?3", nativeQuery = true)
	List<User> findUserListSql(String firstName, String field, String order);
	// 不能倒序排列 TODO
//	@Query(value="SELECT * FROM user WHERE first_name = ?1 ORDER BY ?2 DESC", nativeQuery = true)
	@Query(value="SELECT * FROM user WHERE first_name = ?1 ORDER BY ?2", nativeQuery = true)
	List<User> findUserListSql(String firstName, String sort);
	
	// 不区分大小写
//	@Query(value="SELECT * FROM user WHERE first_name like %?1%", nativeQuery = true)
	// 区分大小写 成功
//	@Query(value="SELECT * FROM user WHERE first_name COLLATE utf8mb4_bin like %?1%", nativeQuery = true)
	@Query(value="SELECT * FROM user WHERE first_name like %?1% COLLATE utf8mb4_bin", nativeQuery = true)
	List<User> findLikeByFirstNameSql(String keywords);
	
	// 分页
	@Query(value="SELECT * FROM user WHERE age > ?1", 
			countQuery = "SELECT count(*) FROM user WHERE age > ?1", 
			nativeQuery = true)
	Page<User> pageUserGreaterThanSql(Integer age, Pageable pageable);
	
	// @Param
	@Query(value="SELECT * FROM user WHERE first_name = :firstName and last_name = :lastName", nativeQuery = true)
	List<User> findByNamesSql(@Param(value = "lastName") String name2, @Param(value = "firstName") String name1);
	
	@Query(value="SELECT * FROM user WHERE first_name = :#{#dto.name1} and last_name = :#{#dto.name2}", nativeQuery = true)
	List<User> findByNamesDtoSql(@Param(value = "dto") NamesDTO dto);

	// 使用 @NamedQuery
	
	List<User> findInNamedQuery(String firstName);
	
	// 使用存储过程
	
	// 存储过程名称
	@Procedure("plus1inout") // value值
	Integer explicitlyNamedPlus1inout(Integer arg);
	
	// 存储过程名称
	@Procedure(procedureName =  "plus1inout")
	Integer plus1inout(Integer arg);
	
	// 自定义的存储过程名称
	@Procedure(name = "User.plus1")
	Integer entityAnnotatedCustomNamedProcedurePlus1io(Integer arg);
	
}
